﻿using DocumentFormat.OpenXml.Office2010.ExcelAc;
using DotNet.Utilities;
using JinfuyuanProject.Entity;
using JinfuyuanProject.Entity.Filter;
using JinfuyuanProject.Entity.ViewModels;
using JinfuyuanProject.Repository;
using JinfuyuanProject.Repository.Interface;
using JinfuyuanProject.Service.Interface;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.SqlServer;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace JinfuyuanProject.Service
{
    public class SalaryService : ISalaryService
    {

        private readonly ISalaryRepository _SalaryRepository;
        private readonly IRoleRepository _RoleRepository;
        private readonly IStaffRepository _StaffRepository;
        private readonly ISalaryDetailsRepository _SalaryDetailsRepository;
        private readonly IContractRepository _ContractRepository;
        private readonly IParturientRepository _ParturientRepository;

        public SalaryService(ISalaryRepository SalaryRepository,IStaffRepository staffRepository,IRoleRepository roleRepository,ISalaryDetailsRepository salaryDetailsRepository,IContractRepository contractRepository,IParturientRepository parturientRepository)
        {
            _SalaryRepository = SalaryRepository;
            _RoleRepository = roleRepository;
            _StaffRepository = staffRepository;
            _SalaryDetailsRepository = salaryDetailsRepository;
            _ContractRepository = contractRepository;
            _ParturientRepository = parturientRepository;
        }
        #region 基础方法
        public bool Delete(Salary Salary)
        {
            return _SalaryRepository.Delete(Salary);
        }

        public IQueryable<Salary> GetList()
        {
            return _SalaryRepository.GetList();
        }

        public Salary GetModel(int Id)
        {
            return _SalaryRepository.GetModel(Id);
        }

        public int Insert(Salary Salary)
        {
            return _SalaryRepository.Insert(Salary);
        }

        public bool Update(Salary Salary)
        {
            return _SalaryRepository.Update(Salary);
        }
        #endregion
        #region 业务方法
        public ResMessage GetSalaryList(SalaryFilter filter)
        {
    
            // 使用 DbFunctions 来进行日期过滤，并确保是在数据库中处理
            var listQuery = from salary in _SalaryRepository.GetList()
                            join staff in _StaffRepository.GetList() on salary.EmpId equals staff.Id
                            join role in _RoleRepository.GetList() on staff.RoleId equals role.Id
                            select new SalaryVM
                            {
                                Id = salary.Id,
                                Name = staff.Name,
                                RoleName = role.RoleName,
                                Numbers = staff.Numbers,
                                BaseSalary = salary.BaseSalary ?? 0,
                                ExtraSalary = salary.ExtraSaraly ?? 0,
                                TotalSalary = salary.TotalSaraly ?? 0,
                                Times = salary.Times // 假设这里有时间字段
                            };
            if (!string.IsNullOrEmpty(filter.d_id))
            {
                // 解析年份和月份
                var yearMonth = filter.d_id.ToString();
                string year = yearMonth.Substring(0, 4);
                string month = "";
                if (yearMonth.Length >= 6)
                {
                    month = yearMonth.Substring(yearMonth.IndexOf('-') + 1);
                }
                // 保持在数据库中进行年份和月份的筛选
                listQuery = listQuery.Where(s => DbFunctions.TruncateTime(s.Times).Value.Year.ToString() == year);

                if (month.Length > 0)
                {
                    listQuery = listQuery.Where(s => DbFunctions.TruncateTime(s.Times).Value.Month.ToString() == month);
                }
            }
            //分页
            if (filter.page!= 0 && filter.limit != 0)
            {
                listQuery = listQuery.OrderByDescending(x=>x.TotalSalary).Skip((filter.page - 1) * filter.limit).Take(filter.limit);
            }
            return ResMessage.Success(listQuery);
        }
        public ResMessage GetSalaryDetailsList(SalaryFilter filter,int s_id)
        {
            Salary salary = _SalaryRepository.GetModel(s_id);
            if (salary == null)
            {
                return ResMessage.Fail("没有找到该薪资信息");
            }
            // 使用 DbFunctions 来进行日期过滤，并确保是在数据库中处理
            int year = ((DateTime)salary.Times).Year;
            int month = ((DateTime)salary.Times).Month;
            if (month == 1)
            {
                year--;
                month = 12;
            }
            else
            {
                month--;
            }
            var list = from sdetail in _SalaryDetailsRepository.GetList().Where(x => x.SalaryId == s_id)
                            join contract in _ContractRepository.GetList() on sdetail.ContractId equals contract.Id
                            join parturient in _ParturientRepository.GetList() on contract.ParturientId equals parturient.Id
                            where sdetail.CreateTime.Year== year && sdetail.CreateTime.Month == month
                            select new SalaryDetailVM
                            {
                                ParturientName = parturient.ParturientName,
                                ContractNumber = contract.ContractNumber,
                                Deposit = sdetail.Proportion,
                                Money = sdetail.Money,
                                Phone = parturient.ParturientPhone,
                                ExtraSalary =sdetail.CommissionMoney
                            };
            if (filter.page != 0 && filter.limit != 0)
            {
                list = list.OrderByDescending(x => x.ExtraSalary).Skip((filter.page - 1) * filter.limit).Take(filter.limit);
            }
            return ResMessage.Success(list);
        }
        public Salary Query(SalaryFilter filter)
        {
            if(filter.EmpId != 0)
            {
                return _SalaryRepository.GetList().OrderByDescending(x=>x.Times).Where(x => x.EmpId == filter.EmpId).FirstOrDefault();
            }
            return null;
        }

        //excel
        public Hashtable GenerateSalaryExcel(string filePath = null)
        {
            try
            {
                // 1. 准备数据
                var data = (from salary in _SalaryRepository.GetList()
                            join staff in _StaffRepository.GetList() on salary.EmpId equals staff.Id
                            join role in _RoleRepository.GetList() on staff.RoleId equals role.Id
                            select new
                            {
                                staff.Name,
                                role.RoleName,
                                staff.Numbers,
                                BaseSalary = salary.BaseSalary ?? 0,
                                ExtraSalary = salary.ExtraSaraly ?? 0,
                                TotalSalary = salary.TotalSaraly ?? 0
                            }).ToList();

                // 2. 创建Excel工作簿
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("员工薪资表");

                // 3. 创建样式
                ICellStyle headerStyle = workbook.CreateCellStyle();
                IFont headerFont = workbook.CreateFont();
                headerFont.FontHeightInPoints = 11;
                headerFont.IsBold = true;
                headerStyle.SetFont(headerFont);
                headerStyle.Alignment = HorizontalAlignment.Center;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                headerStyle.BorderBottom = BorderStyle.Thin;
                headerStyle.BorderLeft = BorderStyle.Thin;
                headerStyle.BorderRight = BorderStyle.Thin;
                headerStyle.BorderTop = BorderStyle.Thin;

                // 标题样式（更大的字体）
                ICellStyle titleStyle = workbook.CreateCellStyle();
                IFont titleFont = workbook.CreateFont();
                titleFont.FontHeightInPoints = 16;
                titleFont.IsBold = true;
                titleStyle.SetFont(titleFont);
                titleStyle.Alignment = HorizontalAlignment.Center;
                titleStyle.VerticalAlignment = VerticalAlignment.Center;
                titleStyle.BorderBottom = BorderStyle.Thin;
                titleStyle.BorderLeft = BorderStyle.Thin;
                titleStyle.BorderRight = BorderStyle.Thin;
                titleStyle.BorderTop = BorderStyle.Thin;

                // 数据单元格样式
                ICellStyle dataCellStyle = workbook.CreateCellStyle();
                dataCellStyle.BorderBottom = BorderStyle.Thin;
                dataCellStyle.BorderLeft = BorderStyle.Thin;
                dataCellStyle.BorderRight = BorderStyle.Thin;
                dataCellStyle.BorderTop = BorderStyle.Thin;

                // 4. 添加标题行（合并A1到F3）
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, 5));

                // 确保所有单元格在合并区域中都被创建
                for (int i = 0; i <= 2; i++)
                {
                    IRow row = sheet.CreateRow(i);
                    for (int j = 0; j <= 5; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.CellStyle = titleStyle;
                    }
                }

                // 设置标题文本
                sheet.GetRow(0).GetCell(0).SetCellValue("职工工资发放表");

                // 5. 添加表头行（第4行）
                IRow headerRow = sheet.CreateRow(3);
                string[] headers = { "姓名", "职务", "员工编号", "底薪", "绩效", "总薪资" };
                for (int i = 0; i < headers.Length; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headers[i]);
                    cell.CellStyle = headerStyle;
                }

                // 6. 填充数据
                int rowIndex = 4;
                foreach (var item in data)
                {
                    IRow row = sheet.CreateRow(rowIndex);
                    CreateCellWithStyle(row, 0, item.Name, dataCellStyle);
                    CreateCellWithStyle(row, 1, item.RoleName, dataCellStyle);
                    CreateCellWithStyle(row, 2, item.Numbers, dataCellStyle);
                    CreateCellWithStyle(row, 3, (double)item.BaseSalary, dataCellStyle);
                    CreateCellWithStyle(row, 4, (double)item.ExtraSalary, dataCellStyle);
                    CreateCellWithStyle(row, 5, (double)item.TotalSalary, dataCellStyle);
                    rowIndex++;
                }

                // 7. 计算总计
                double totalTotalSalary = (double)data.Sum(item => item.TotalSalary);

                // 8. 添加总计行（完整代码）
                IRow totalRow = sheet.CreateRow(rowIndex);

                // 第一列：总计标签
                CreateCellWithStyle(totalRow, 0, "总计", headerStyle);

                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 5));

                // 创建并设置合并区域的单元格
                for (int i = 1; i <= 5; i++)
                {
                    ICell cell = totalRow.CreateCell(i);
                    cell.CellStyle = dataCellStyle;
                    if (i == 1) // 只在第一个单元格设置值
                    {
                        cell.SetCellValue(totalTotalSalary);
                    }
                }

                rowIndex++;  // 行索引递增

                // 9. 添加日期行
                IRow dateRow = sheet.CreateRow(rowIndex);
                CreateCellWithStyle(dateRow, 0, "日期", headerStyle);

                // 合并日期单元格
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 5));

                // 创建并设置合并区域的单元格
                for (int i = 1; i <= 5; i++)
                {
                    ICell cell = dateRow.CreateCell(i);
                    cell.CellStyle = dataCellStyle;
                    if (i == 1) // 只在第一个单元格设置值
                    {
                        cell.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));
                    }
                }

                // 10. 自动调整列宽
                for (int i = 0; i < headers.Length; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                // 11. 设置保存路径
                string savePath = string.IsNullOrEmpty(filePath)
                    ? Path.Combine(@"D:\im", $"员工薪资_{DateTime.Now:yyyyMMddHHmmss}.xls")
                    : filePath;

                // 确保目录存在
                Directory.CreateDirectory(Path.GetDirectoryName(savePath));

                // 12. 保存文件
                using (FileStream fs = new FileStream(savePath, FileMode.Create))
                {
                    workbook.Write(fs);
                }

                return new Hashtable
                {
                    ["code"] = 0,
                    ["message"] = $"Excel文件已生成: {savePath}",
                    ["path"] = savePath
                };
            }
            catch (Exception ex)
            {
                return new Hashtable
                {
                    ["code"] = -1,
                    ["message"] = $"生成Excel失败: {ex.Message}"
                };
            }
        }

        // 辅助方法：创建单元格并设置样式
        // 辅助方法：创建单元格并设置样式
        private void CreateCellWithStyle(IRow row, int columnIndex, object value, ICellStyle style)
        {
            ICell cell = row.CreateCell(columnIndex);

            if (value is string)
            {
                cell.SetCellValue((string)value);
            }
            else if (value is double)
            {
                cell.SetCellValue((double)value);
            }
            else if (value is int)
            {
                cell.SetCellValue((int)value);
            }
            // 将总结与时间居中
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            cell.CellStyle = style;
        }

    }
}


#endregion

